package com.ccjq.shosestore.product.dao.impl;

import com.ccjq.shosestore.core.dbmanger.DbConnection;
import com.ccjq.shosestore.product.dao.HomeDao;
import com.ccjq.shosestore.product.po.HomePo;
import com.ccjq.shosestore.product.t_productPo.DetailsPo;
import com.ccjq.shosestore.product.t_productPo.productPo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class HomeDaoImpl implements HomeDao {
    public List<HomePo> selectAll() {
        List<HomePo> list = new ArrayList<HomePo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select category_id,name from t_category";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            res = pre.executeQuery();
            while(res.next()) {
                HomePo  homePo = new HomePo();
                homePo.setCategory_id(res.getString(1));
                homePo.setName(res.getString(2));
                list.add(homePo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }

    public List<productPo> selectNew() {
        List<productPo> list = new ArrayList<productPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select * from t_product where movalty_status=1 and rownum<=6 order by publish_date desc ";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            res = pre.executeQuery();
            while(res.next()) {
                productPo  productpo = new productPo();
                productpo.setProduct_id(res.getString(1));
                productpo.setDescription(res.getString(2));
                productpo.setPrice(res.getDouble(3));
                productpo.setModel(res.getString(4));
                productpo.setFeature(res.getString(5));
                productpo.setImgpath(res.getString(6));
                productpo.setMovalty_status(res.getInt(7));
                productpo.setPublish_date(res.getString(8));
                productpo.setCategory_id(res.getString(9));
                list.add(productpo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }

    public List<productPo> selectReMai() {
        List<productPo> list = new ArrayList<productPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select * from t_product where movalty_status=1 and rownum<=3 order by publish_date desc ";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            res = pre.executeQuery();
            while(res.next()) {
                productPo  productpo = new productPo();
                productpo.setProduct_id(res.getString(1));
                productpo.setDescription(res.getString(2));
                productpo.setPrice(res.getDouble(3));
                productpo.setModel(res.getString(4));
                productpo.setFeature(res.getString(5));
                productpo.setImgpath(res.getString(6));
                productpo.setMovalty_status(res.getInt(7));
                productpo.setPublish_date(res.getString(8));
                productpo.setCategory_id(res.getString(9));
                list.add(productpo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }

    public int pageCount() {
        int count = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select count(*) from t_product";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            res = pre.executeQuery();
            if(res.next()) {
                count=res.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return count;
    }

    public int pageCountId(String category_id) {
        int count = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select count(*) from t_product where category_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,category_id);
            res = pre.executeQuery();
            if(res.next()) {
                count=res.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return count;
    }

    /**
     *查询分页全部
     * @param pageSize 每页显示的行数
     * @param startRow 当前行
     * @return
     */
    public List<productPo> selectFengYeAll(int startRow,int pageSize) {
        List<productPo> list = new ArrayList<productPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        //String sql = "select * from (select t.*, rownum rn from t_product t )t2 where t2.rn >=? and t2.rn <=?";
        String sql = " select * from t_product where product_id not in(select product_id from t_product where rownum<?)and rownum<=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setInt(1,startRow);
            pre.setInt(2,pageSize);
            res = pre.executeQuery();
            while(res.next()) {
                productPo  productpo = new productPo();
                productpo.setProduct_id(res.getString(1));
                productpo.setDescription(res.getString(2));
                productpo.setPrice(res.getDouble(3));
                productpo.setModel(res.getString(4));
                productpo.setFeature(res.getString(5));
                productpo.setImgpath(res.getString(6));
                productpo.setMovalty_status(res.getInt(7));
                productpo.setPublish_date(res.getString(8));
                productpo.setCategory_id(res.getString(9));
                list.add(productpo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }

    public List<productPo> selectFengYe(String category_id, int startRow, int pageSize) {
        List<productPo> list = new ArrayList<productPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select * from (select t.*, rownum rn from t_product t where t.category_id=?)t2 where t2.rn >=? and t2.rn <=?";
        //String sql = " select * from t_product where product_id not in(select product_id from t_product where rownum<=? and category_id=?)and rownum<=? and category_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,category_id);
            pre.setInt(2,startRow);
            pre.setInt(3,pageSize);
            res = pre.executeQuery();
            while(res.next()) {
                productPo  productpo = new productPo();
                productpo.setProduct_id(res.getString(1));
                productpo.setDescription(res.getString(2));
                productpo.setPrice(res.getDouble(3));
                productpo.setModel(res.getString(4));
                productpo.setFeature(res.getString(5));
                productpo.setImgpath(res.getString(6));
                productpo.setMovalty_status(res.getInt(7));
                productpo.setPublish_date(res.getString(8));
                productpo.setCategory_id(res.getString(9));
                list.add(productpo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return list;
    }

    public DetailsPo selectDatails(String product_id) {
        DetailsPo  productpo = new DetailsPo();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = " select * from t_product a full join t_category b on a.category_id=b.category_id join t_stock c on a.product_id=c.product_id where a.product_id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1,product_id);
            res = pre.executeQuery();
            if(res.next()) {
                productpo.setProduct_id(res.getString(1));
                productpo.setDescription(res.getString(2));
                productpo.setPrice(res.getDouble(3));
                productpo.setModel(res.getString(4));
                productpo.setFeature(res.getString(5));
                productpo.setImgpath(res.getString(6));
                productpo.setMovalty_status(res.getInt(7));
                productpo.setPublish_date(res.getString(8));
                productpo.setCategory_id(res.getString(9));
                productpo.setName(res.getString(11));
                productpo.setQuantity(res.getInt(13));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(res, pre, con);
        }
        return productpo;
    }

}
